package Limit.Event.Easter;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import l1j.server.L1DatabaseFactory;
import l1j.server.server.utils.SQLUtil;

public class Event_EasterTicketTable 
{
	
	private String _FirstNum;
	private String _SecondNum;
	private String _ThirdNum;
	private String _FourthNum;
	private String _FifthNum;
	private String _SixthNum;
	private String _SeventhNum;
	private String _EighthNum;
	private int _Count;
	
	public String getFirstNum()
	{
		return _FirstNum;
	}
	
	public void setFirstNum(String i)
	{
		_FirstNum = i;
	}
	
	public String getSecondNum()
	{
		return _SecondNum;
	}
	
	public void setSecondNum(String i)
	{
		_SecondNum = i;
	}
	
	public String getThirdNum()
	{
		return _ThirdNum;
	}
	
	public void setThirdNum(String i)
	{
		_ThirdNum = i;
	}
	
	public String getFourthNum()
	{
		return _FourthNum;
	}
	
	public void setFourthNum(String i)
	{
		_FourthNum = i;
	}
	
	public String getFifthNum()
	{
		return _FifthNum;
	}
	
	public void setFifthNum(String i)
	{
		_FifthNum = i;
	}
	
	public String getSixthNum()
	{
		return _SixthNum;
	}
	
	public void setSixthNum(String i)
	{
		_SixthNum = i;
	}
	
	public String getSeventhNum()
	{
		return _SeventhNum;
	}
	
	public void setSeventhNum(String i)
	{
		_SeventhNum = i;
	}
	
	public String getEighthNum()
	{
		return _EighthNum;
	}
	
	public void setEighthNum(String i)
	{
		_EighthNum = i;
	}
	
	public int getCount()
	{
		return _Count;
	}
	
	public void setCount(int i)
	{
		_Count = i;
	}
	
	public void StoreTickets(int charid, String chaename, String tickets)
	{	
		Connection con = null;	
		PreparedStatement pstm = null;
		
		try {
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("INSERT INTO event_easter_tickets SET char_id=?, char_name=?, tickets=?, datetime=SYSDATE()");
			pstm.setInt(1, charid);
			pstm.setString(2, chaename);
			pstm.setString(3, tickets);
			pstm.execute();
		} 
		catch (Exception e) 
		{
		} finally 
		{
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public String[] LoadTickets(int charid) 
	{
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		int i = 0;
		int j = TicketsSize(charid);
		String[] result = new String[j];
		
		for(i = 0; i < j; i++)
			result[i] = "";
		
		try 
		{
			i=1;
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT tickets FROM event_easter_tickets WHERE char_id=? Order By datetime");
			pstm.setInt(1, charid);
			rs = pstm.executeQuery();
			
			while (rs.next()) 
			{
				result[i] = "您所選擇的抽獎號碼: "+rs.getString("tickets");
				i++;
			}
		}
		catch (SQLException e)
		{ 
		}
		finally 
		{
			SQLUtil.close(rs, pstm, con);
		}
		return result;
	}
	
	public int TicketsSize(int charid)
	{
		int i = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try 
		{
			i=1;
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT * FROM event_easter_tickets WHERE char_id=? Order By datetime");
			pstm.setInt(1, charid);
			rs = pstm.executeQuery();
			
			while (rs.next()) 
			{
				i++;
			}
		}
		catch (SQLException e) 
		{ 
		}
		finally
		{
			SQLUtil.close(rs, pstm, con);
		}
		return i;
	}
	
	public void loadCount(int Num) 
	{
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;		
		try 
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT * FROM event_easter_count Where num=?");
			pstm.setInt(1, Num);
			rs = pstm.executeQuery();
			
			while (rs.next()) 
			{
				setCount(rs.getInt("count"));
			}			
		}	
		catch (Exception e) 
		{ 
		}
		finally 
		{
			SQLUtil.close(rs, pstm, con);
		}
	}
	
	public void updateCount(int Num, int Count)
	{
		Connection con = null;
		PreparedStatement pstm = null;
		try 
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("UPDATE event_easter_count SET count=? WHERE num=?");
			pstm.setInt(1, Count);
			pstm.setInt(2, Num);
			pstm.execute();
		} 
		catch (Exception e) 
		{
		} 
		finally 
		{
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public int countMarkTickets(int charid)
	{
		int result = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try 
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("SELECT count(*) as cnt FROM event_easter_mark_ticket WHERE char_id=?");
			pstm.setInt(1, charid);
			rs = pstm.executeQuery();
			
			if (rs.next()) 
			{
				result = rs.getInt("cnt");
			}
		} 
		catch (SQLException e) 
		{
		} finally 
		{
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return result;
	}
	
	public void StoreMarkTicket(int charid, String chaename) 
	{	
		Connection con = null;	
		PreparedStatement pstm = null;
		try
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("INSERT INTO event_easter_mark_ticket SET char_id=?, char_name=?, datetime=SYSDATE()");
			pstm.setInt(1, charid);
			pstm.setString(2, chaename);
			pstm.execute();
		} 
		catch (Exception e) 
		{
		} 
		finally 
		{
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public void LoadMarkTicket(int charid) 
	{
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try 
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT * FROM event_easter_mark_ticket WHERE char_id=?");
			pstm.setInt(1, charid);
			rs = pstm.executeQuery();
			
			while (rs.next()) 
			{
				setFirstNum(rs.getString("firstnum"));
				setSecondNum(rs.getString("secondnum"));
				setThirdNum(rs.getString("thirdnum"));
				setFourthNum(rs.getString("fourthnum"));
				setFifthNum(rs.getString("fifthnum"));
				setSixthNum(rs.getString("Sixthnum"));
				setSeventhNum(rs.getString("seventhnum"));
				setEighthNum(rs.getString("eighthnum"));
			}
		}
		catch (SQLException e)
		{ 
		}
		finally 
		{
			SQLUtil.close(rs, pstm, con);
		}
	}
	
	public void updateMarkTicket(int charid, String num, String mark) 
	{
		Connection con = null;
		PreparedStatement pstm = null;
		try 
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con
					.prepareStatement("UPDATE event_easter_mark_ticket SET " + num + "=? WHERE char_id=?");
			pstm.setString(1, mark);
			pstm.setInt(2, charid);
			pstm.execute();
			
			if(num.equalsIgnoreCase("firstnum"))
			{
				setFirstNum(mark);
			} 
			
			else if(num.equalsIgnoreCase("secondnum"))
			{
				setSecondNum(mark);
			} 
			
			else if(num.equalsIgnoreCase("thirdnum"))
			{
				setThirdNum(mark);
			} 
			
			else if(num.equalsIgnoreCase("fourthnum"))
			{
				setFourthNum(mark);
			} 
			
			else if(num.equalsIgnoreCase("fifthnum"))
			{
				setFifthNum(mark);
			} 
			
			else if(num.equalsIgnoreCase("sixthnum"))
			{
				setSixthNum(mark);
			} 
			
			else if(num.equalsIgnoreCase("seventhnum"))
			{
				setSeventhNum(mark);
			} 
			
			else if(num.equalsIgnoreCase("eighthnum"))
			{
				setEighthNum(mark);
			}			
		} 
		catch (Exception e) 
		{
		}
		finally 
		{
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public void deleteMarkTicket(int charid)
	{
		Connection con = null;	
		PreparedStatement pstm = null;
		try 
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("DELETE FROM event_easter_mark_ticket WHERE char_id=?");
			pstm.setInt(1,charid);
			pstm.execute();
		} 
		catch (Exception e) 
		{
		} 
		finally 
		{
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
	
	public int countOYTickets(String ticketnum) 
	{
		int result = 0;
		Connection con = null;
		PreparedStatement pstm = null;
		ResultSet rs = null;
		try 
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("SELECT count(*) as cnt FROM event_easter_tickets WHERE tickets=?");
			pstm.setString(1, ticketnum);
			rs = pstm.executeQuery();
			
			if (rs.next())
			{
				result = rs.getInt("cnt");
			}
		} 
		catch (SQLException e) 
		{
		} 
		finally 
		{
			SQLUtil.close(rs);
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
		return result;
	}
	
	public void renewMarkTicket(int charid, String clean) 
	{
		Connection con = null;
		PreparedStatement pstm = null;
		try 
		{
			con = L1DatabaseFactory.getInstance().getConnection();
			pstm = con.prepareStatement("UPDATE event_easter_mark_ticket SET firstnum=?, secondnum=?, thirdnum=?, fourthnum=?, fifthnum=?, sixthnum=?, seventhnum=?, eighthnum=? WHERE char_id=?");
			int i = 0;
			pstm.setString(++i, clean);
			pstm.setString(++i, clean);
			pstm.setString(++i, clean);
			pstm.setString(++i, clean);
			pstm.setString(++i, clean);
			pstm.setString(++i, clean);
			pstm.setString(++i, clean);
			pstm.setString(++i, clean);
			pstm.setInt(++i, charid);
			pstm.execute();			
		} 
		catch (Exception e) 
		{
		} 
		finally 
		{
			SQLUtil.close(pstm);
			SQLUtil.close(con);
		}
	}
}
